import xlwt
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
from quote_folder.Db_connect import DB_connect
from selenium.webdriver.common.by import By
from selenium import webdriver
from bs4 import BeautifulSoup
import time
import random
import re
from 查询并更新系统专利状态 import is_search_ok


class Patent_Data:
    def __init__(self, mark):
        self.get_url = "http://cpquery.cnipa.gov.cn/"
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.bigdata_con = DB_connect().online_big_data()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.redis_con = DB_connect().patents_reids()  # redis连接
        self.mark = mark
        self.info_list = []
        self.is_search_ok = is_search_ok

    def login_gov(self, ):
        """ 登录中国及多国 """
        # profile = webdriver.FirefoxOptions()
        # profile.set_preference('network.proxy.type', 1)
        # browser = webdriver.Firefox(options=profile, executable_path=r'D:\工具\geckodriver-v0.15.0-win64\geckodriver.exe')
        # browser.get(self.get_url)
        # time.sleep(5)
        # WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "username"))).clear()
        # browser.find_element_by_id('username').send_keys('42264')
        # time.sleep(2)
        # browser.find_element_by_id('password').clear()
        # browser.find_element_by_id('password').send_keys('Xhhg0613@@')
        # # time.sleep(random.randint(30, 50))
        # self.is_search_ok(browser)
        # current_url = browser.current_url
        # return browser, current_url

        profile = webdriver.FirefoxOptions()
        profile.set_preference('network.proxy.type', 1)
        browser = webdriver.Firefox(options=profile, executable_path=r'D:\工具\geckodriver-v0.15.0-win64\geckodriver.exe')
        browser.get(self.get_url)
        time.sleep(5)
        WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "username1"))).clear()
        browser.find_element_by_id('username1').send_keys('18240721210')
        browser.find_element_by_id('password1').clear()
        browser.find_element_by_id('password1').send_keys('Zx123456~')
        time.sleep(5)
        is_search_ok(browser)
        current_url = browser.current_url
        return browser, current_url

    def By_payment_information(self, soup, sqh):
        yjf_div_list = soup.select(("div[id='yjfid'] table tr"))  # 已缴费
        yjf_list = []
        if len(yjf_div_list) > 1:

            for jf_tsg in yjf_div_list[1:]:
                yjf_item = {}
                jf_td_list = jf_tsg.select("td > span")
                yjf_item['sqh'] = sqh.strip()
                yjf_item["cost_category"] = jf_td_list[0]["title"]  # 费用种类
                yjf_item["payment_amount"] = jf_td_list[1]["title"]  # 缴费金额
                yjf_item["pay_date"] = jf_td_list[2]["title"]  # 缴费日期
                yjf_item["name_payer"] = jf_td_list[3]["title"]  # 缴费人
                # yjf_item["note_the_code"] = jf_td_list[4]["title"]  # 票据代码
                # yjf_item["ticket_number"] = jf_td_list[5]["title"]  # 票据号码
                print("已缴费数据：", yjf_item)
                self.info_list.append(yjf_item)

    def save_excel(self, info_list):
        print(info_list)
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '编号')
        sheet.write(0, 1, '申请号')
        sheet.write(0, 2, '专利名称')
        sheet.write(0, 3, '申请人')
        sheet.write(0, 4, '申请日')
        sheet.write(0, 5, '国知局状态')
        sheet.write(0, 6, '联系人')
        sheet.write(0, 7, '电话')
        sheet.write(0, 8, '发明人')
        sheet.write(0, 9, '代理机构')
        sheet.write(0, 10, '缴费人')
        sheet.write(0, 11, '缴费金额')
        sheet.write(0, 12, '缴费时间')
        sheet.write(0, 13, '缴费类型')

        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 1, info_list[i]['sqh'])
                sheet.write(i + 1, 10, info_list[i]['name_payer'])
                sheet.write(i + 1, 11, info_list[i]['payment_amount'])
                sheet.write(i + 1, 12, info_list[i]['pay_date'])
                sheet.write(i + 1, 13, info_list[i]['cost_category'])
            workbook.save("欧阳浩案子.xls")

    def get_html(self, sqh, browser, current_url):
        sqh = re.sub(r'ZL', '', sqh)
        pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key:zhuanlilx', current_url))
        url = re.sub(pat, sqh, current_url)
        browser.get(url)
        count_page = browser.page_source
        soup = BeautifulSoup(count_page, "lxml")
        self.By_payment_information(soup, sqh)
        self.save_excel(self.info_list)

    def get_DBdata(self, sqh):
        sql = f"SELECT money_type, money, close_date FROM annual_fee_information WHERE application_number = '{sqh}'"
        self.bigdata_cur.execute(sql)
        data = self.bigdata_cur.fetchall()
        return data

    def run(self):
        """入口函数"""
        if self.mark == "1":
            browser, current_url = self.login_gov()
            with open("sqh.txt", "r") as fw:
                ss = 0
                for application_number in fw:
                    if ss >= 500:
                        break
                    # print(f"开始抓取 {application_number}")
                    ss += 1
                    self.get_html(application_number, browser, current_url)  # 获取专利申请页面
                    # self.get_powerday_html(application_number, browser, )  # 授权日
                    browser.close()
        if self.mark == "2":
            with open("sqh.txt", "r") as fw:
                ss = 0
                for application_number in fw:
                    if ss >= 500:
                        break
                    ss += 1
                    self.get_DBdata(application_number)
        self.bigdata_con.commit()
        self.bigdata_cur.close()
        self.bigdata_con.close()
        self.develop_cur.close()
        self.develop_con.close()


if __name__ == '__main__':
    mark = "1"  # 1 查询抓取网站数据  2 抓取数据库数据
    func = Patent_Data(mark)
    func.run()  # 2022102024221
